package com.txy.dao.impl;

import com.itany.jdbc.core.JdbcTemplate;
import com.itany.jdbc.util.RowMapper;
import com.txy.dao.EmployeeDao;
import com.txy.entity.Employee;
import com.txy.mapper.EmployeeMapper;
import com.txy.vo.EmployeeParam;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Author：汤小洋
 * Date：2022-11-08 16:17
 * Description：<描述>
 */
public class EmployeeDaoImpl implements EmployeeDao {

    JdbcTemplate<Employee> jt = new JdbcTemplate<>();
    RowMapper<Employee> rm = new EmployeeMapper();

    @Override
    public List<Employee> selectAll() {
        String sql = "select e.*,d.name dept_name,d.loc from t_emp e,t_dept d where e.dept_id=d.id";
        return jt.query(sql,rm);
    }

    @Override
    public void deleteById(int id) {
        String sql = "delete from t_emp where id=?";
        jt.update(sql,id);
    }

    @Override
    public void insert(Employee emp) {
        String sql = "insert into t_emp values (null,?,?,?,?,?,?)";
        jt.update(sql,emp.getName(),emp.getAge(),emp.getSex(),emp.getHeight(),emp.getBirthday(),emp.getDepartment().getId());
    }

    @Override
    public Employee selectById(int id) {
        String sql = "select e.*,d.name dept_name,d.loc from t_emp e,t_dept d where e.dept_id=d.id and e.id=?";
        return jt.queryForObject(sql,rm,id);
    }

    @Override
    public void update(Employee emp) {
        String sql = "update t_emp set name=?,age=?,sex=?,height=?,birthday=?,dept_id=? where id=?";
        jt.update(sql,emp.getName(),emp.getAge(),emp.getSex(),emp.getHeight(),emp.getBirthday(),emp.getDepartment().getId(),emp.getId());
    }

    @Override
    public List<Employee> selectByPage(int pageNo, int pageSize, EmployeeParam param) {
        StringBuffer sql = new StringBuffer()
                .append(" select ")
                .append("   e.*,d.name dept_name,d.loc ")
                .append(" from ")
                .append("   t_emp e,t_dept d ")
                .append(" where ")
                .append("   e.dept_id=d.id ");

        List params = new ArrayList();
        if(param.getName() != null && !"".equals(param.getName())){
            sql.append(" and e.name like ? ");
            params.add("%"+param.getName()+"%");
        }
        if(param.getSex() != null && !"".equals(param.getSex())){
            sql.append(" and e.sex=? ");
            params.add(param.getSex());
        }
        if(param.getDeptId() != 0){
            sql.append(" and e.dept_id=? ");
            params.add(param.getDeptId());
        }

        sql.append(" limit ?,? ");
        params.add((pageNo-1)*pageSize);
        params.add(pageSize);

        return jt.query(sql.toString(),rm,params.toArray());
    }

    @Override
    public int selectCount(EmployeeParam param) {
        StringBuffer sql = new StringBuffer()
                .append(" select ")
                .append("   count(e.id) ")
                .append(" from ")
                .append("   t_emp e,t_dept d ")
                .append(" where ")
                .append("   e.dept_id=d.id ");

        List params = new ArrayList();
        if(param.getName() != null && !"".equals(param.getName())){
            sql.append(" and e.name like ? ");
            params.add("%"+param.getName()+"%");
        }
        if(param.getSex() != null && !"".equals(param.getSex())){
            sql.append(" and e.sex=? ");
            params.add(param.getSex());
        }
        if(param.getDeptId() != 0){
            sql.append(" and e.dept_id=? ");
            params.add(param.getDeptId());
        }

        JdbcTemplate<Integer> jt = new JdbcTemplate<>();
        int count = jt.queryForObject(sql.toString(), new RowMapper<Integer>() {
            @Override
            public Integer mapRow(ResultSet rs) throws SQLException {
                return rs.getInt(1);
            }
        },params.toArray());
        return count;
    }

}
